package com.durablemethods.tsmserver.home;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.durablemethods.tsmserver.model.Trade;
import com.google.appengine.api.rdbms.AppEngineDriver;


public class TradesHome {

	public List<Trade> getTrades(PrintWriter printWriter) {
		List<Trade> returnTrades = new ArrayList<Trade>();
		Connection c = null;
	      try {
	    	//printWriter.println("Register Driver - pre");
			DriverManager.registerDriver(new AppEngineDriver());
			//printWriter.println("Register Driver -- done -- getting connections");
			c = DriverManager.getConnection("jdbc:google:rdbms://durablemethods.com:duravest:tsm","jamie","catal0g");
			//printWriter.println("We have a connection");
			Statement s = c.createStatement();
			//printWriter.println(getQuery());
			ResultSet rs = s.executeQuery(getQuery());
			while (rs.next()){
				Trade returnTrade = new Trade();
				returnTrade.setTeam(rs.getString("Team"));
				returnTrade.setTrader(rs.getString("Trader"));
				returnTrade.setQuanitity(rs.getInt("Quantity"));
				returnTrade.setPrice(rs.getDouble("Price"));
				returnTrade.setTradeTimestamp(rs.getTimestamp("Transaction_Date"));
				returnTrades.add(returnTrade);
			}
		} catch (SQLException e) {
			//printWriter.println("Error found");
			//printWriter.println(e.getMessage());
			e.printStackTrace();
		}
		return returnTrades;
	}

	private String getQuery() {
		StringBuilder builder = new StringBuilder();
		builder.append("select traders.name as Trader, teams.name as Team,trades.quantity as Quantity, trades.price as Price, subtime(trades.transaction_date,'06:00:00.0') as Transaction_Date");
		builder.append(" from TEM_SPORTS.TRADES trades, TEM_SPORTS.TRADERS traders, TEM_SPORTS.TEAMS teams");
		builder.append(" where traders.id !=1");
		builder.append(" and trades.trader_id = traders.id");
		builder.append(" and trades.team_id = teams.id");
		builder.append(" and trades.type='T'");
		builder.append(" order by trades.id desc,  quantity desc");
		return builder.toString();
	}

}
